package fun.stgoder.easydarwin.jobs.comm.util;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import fun.stgoder.easydarwin.jobs.comm.Constants;
import fun.stgoder.easydarwin.jobs.comm.Sql;
import fun.stgoder.easydarwin.jobs.comm.model.Page;
import fun.stgoder.easydarwin.jobs.comm.model.Param;
import org.sql2o.Connection;
import org.sql2o.Query;
import org.sql2o.Sql2o;

import javax.sql.DataSource;
import java.util.List;

public class SqlUtil {

    private static DataSource dataSource;

    private static Sql2o sql2o;

    public static void init() {

        HikariConfig config = new HikariConfig();

        config.setPoolName("easydarwin-jobs-cp");

        config.setDriverClassName("org.sqlite.JDBC");

        config.setJdbcUrl("jdbc:sqlite:" + Constants.EASYDARWIN_JOBS_DB_PATH);

        config.setMaximumPoolSize(2);

        dataSource = new HikariDataSource(config);

        sql2o = new Sql2o(dataSource);
    }

    public static void init(DataSource dataSource) {
        SqlUtil.dataSource = dataSource;

        sql2o = new Sql2o(dataSource);
    }

    public static <T> List<T> select(String sql, Class<T> cls) {
        try (Connection conn = SqlUtil.open()) {
            List<T> list = conn.createQuery(sql).executeAndFetch(cls);
            return list;
        }
    }

    public static <T> List<T> select(String sql, Param param, Class<T> cls) {
        try (Connection conn = SqlUtil.open()) {
            Query query = conn.createQuery(sql);
            for (Param.NameValue nv : param.params()) {
                query.addParameter(nv.getName(), nv.getValue());
            }
            List<T> list = query.executeAndFetch(cls);
            return list;
        }
    }

    public static <T> List<T> select(Connection conn, String sql, Class<T> cls) {
        List<T> list = conn.createQuery(sql).executeAndFetch(cls);
        return list;
    }

    public static <T> List<T> select(Connection conn, String sql, Param param, Class<T> cls) {
        Query query = conn.createQuery(sql);
        for (Param.NameValue nv : param.params()) {
            query.addParameter(nv.getName(), nv.getValue());
        }
        List<T> list = query.executeAndFetch(cls);
        return list;
    }

    public static <T> Page<T> selectPage(String sql, Class<T> cls, int page, int pageSize) {
        try (Connection conn = SqlUtil.open()) {
            List<T> datas = conn
                    .createQuery("SELECT * FROM ( " + sql + " ) tmp LIMIT " + (page - 1) * pageSize + "," + pageSize)
                    .executeAndFetch(cls);
            Long total = conn.createQuery("SELECT COUNT(1) FROM ( " + sql + " ) tmp").executeScalar(Long.class);
            Page<T> pageData = new Page<T>(page, pageSize, total, datas);
            return pageData;
        }
    }

    public static <T> Page<T> selectPage(String sql, Param param, Class<T> cls, int page, int pageSize) {
        try (Connection conn = SqlUtil.open()) {
            Query countQuery = conn.createQuery("SELECT COUNT(1) FROM ( " + sql + " ) tmp");
            Query dataQuery = conn
                    .createQuery("SELECT * FROM ( " + sql + " ) tmp LIMIT " + (page - 1) * pageSize + "," + pageSize);

            for (Param.NameValue nv : param.params()) {
                dataQuery.addParameter(nv.getName(), nv.getValue());
                countQuery.addParameter(nv.getName(), nv.getValue());
            }

            long total = countQuery.executeScalar(Long.class);
            List<T> datas = dataQuery.executeAndFetch(cls);

            Page<T> pageData = new Page<T>(page, pageSize, total, datas);
            return pageData;
        }
    }

    public static <T> Page<T> selectPage(Connection conn, String sql, Param param, Class<T> cls, int page,
                                         int pageSize) {
        Query countQuery = conn.createQuery("SELECT COUNT(1) FROM ( " + sql + " ) tmp");
        Query dataQuery = conn
                .createQuery("SELECT * FROM ( " + sql + " ) tmp LIMIT " + (page - 1) * pageSize + "," + pageSize);

        for (Param.NameValue nv : param.params()) {
            dataQuery.addParameter(nv.getName(), nv.getValue());
            countQuery.addParameter(nv.getName(), nv.getValue());
        }

        long total = countQuery.executeScalar(Long.class);
        List<T> datas = dataQuery.executeAndFetch(cls);

        Page<T> pageData = new Page<T>(page, pageSize, total, datas);
        return pageData;
    }

    public static <T> T selectOne(String sql, Class<T> cls) {
        try (Connection conn = SqlUtil.open()) {
            T t = conn.createQuery(sql).executeAndFetchFirst(cls);
            return t;
        }
    }

    public static <T> T selectOne(String sql, Param param, Class<T> cls) {
        try (Connection conn = SqlUtil.open()) {
            Query query = conn.createQuery(sql);
            for (Param.NameValue nv : param.params()) {
                query.addParameter(nv.getName(), nv.getValue());
            }
            T t = query.executeAndFetchFirst(cls);
            return t;
        }
    }

    public static <T> T selectOne(Connection conn, String sql, Class<T> cls) {
        T t = conn.createQuery(sql).executeAndFetchFirst(cls);
        return t;
    }

    public static <T> T selectOne(Connection conn, String sql, Param param, Class<T> cls) {
        Query query = conn.createQuery(sql);
        for (Param.NameValue nv : param.params()) {
            query.addParameter(nv.getName(), nv.getValue());
        }
        T t = query.executeAndFetchFirst(cls);
        return t;
    }

    public static <T> T selectScalar(String sql, Class<T> cls) {
        try (Connection conn = SqlUtil.open()) {
            T t = conn.createQuery(sql).executeScalar(cls);
            return t;
        }
    }

    public static <T> T selectScalar(String sql, Param param, Class<T> cls) {
        try (Connection conn = SqlUtil.open()) {
            Query query = conn.createQuery(sql);
            for (Param.NameValue nv : param.params()) {
                query.addParameter(nv.getName(), nv.getValue());
            }
            T t = query.executeScalar(cls);
            return t;
        }
    }

    public static <T> T selectScalar(Connection conn, String sql, Class<T> cls) {
        T t = conn.createQuery(sql).executeScalar(cls);
        return t;
    }

    public static <T> T selectScalar(Connection conn, String sql, Param param, Class<T> cls) {
        Query query = conn.createQuery(sql);
        for (Param.NameValue nv : param.params()) {
            query.addParameter(nv.getName(), nv.getValue());
        }
        T t = query.executeScalar(cls);
        return t;
    }

    public static <T> List<T> selectScalarList(String sql, Class<T> cls) {
        try (Connection conn = SqlUtil.open()) {
            List<T> list = conn.createQuery(sql).executeScalarList(cls);
            return list;
        }
    }

    public static <T> List<T> selectScalarList(String sql, Param param, Class<T> cls) {
        try (Connection conn = SqlUtil.open()) {
            Query query = conn.createQuery(sql);
            for (Param.NameValue nv : param.params()) {
                query.addParameter(nv.getName(), nv.getValue());
            }
            List<T> list = query.executeScalarList(cls);
            return list;
        }
    }

    public static <T> List<T> selectScalarList(Connection conn, String sql, Class<T> cls) {
        List<T> list = conn.createQuery(sql).executeScalarList(cls);
        return list;
    }

    public static <T> List<T> selectScalarList(Connection conn, String sql, Param param, Class<T> cls) {
        Query query = conn.createQuery(sql);
        for (Param.NameValue nv : param.params()) {
            query.addParameter(nv.getName(), nv.getValue());
        }
        List<T> list = query.executeScalarList(cls);
        return list;
    }

    public static long count(String sql) {
        try (Connection conn = SqlUtil.open()) {
            Long count = conn.createQuery(sql).executeScalar(Long.class);
            return count;
        }
    }

    public static long count(String sql, Param param) {
        try (Connection conn = SqlUtil.open()) {
            Query query = conn.createQuery(sql);
            for (Param.NameValue nv : param.params()) {
                query.addParameter(nv.getName(), nv.getValue());
            }
            Long count = query.executeScalar(Long.class);
            return count;
        }
    }

    public static long count(Connection conn, String sql) {
        Long count = conn.createQuery(sql).executeScalar(Long.class);
        return count;
    }

    public static long count(Connection conn, String sql, Param param) {
        Query query = conn.createQuery(sql);
        for (Param.NameValue nv : param.params()) {
            query.addParameter(nv.getName(), nv.getValue());
        }
        Long count = query.executeScalar(Long.class);
        return count;
    }

    public static int insert(String sql) {
        try (Connection conn = SqlUtil.open()) {
            return executeUpdate(conn, sql);
        }
    }

    public static int insert(String sql, Param param) {
        try (Connection conn = SqlUtil.open()) {
            return executeUpdate(conn, sql, param);
        }
    }

    public static int insert(Connection conn, String sql) {
        return executeUpdate(conn, sql);
    }

    public static int insert(Connection conn, String sql, Param param) {
        return executeUpdate(conn, sql, param);
    }

    public static int delete(String sql) {
        try (Connection conn = SqlUtil.open()) {
            return executeUpdate(conn, sql);
        }
    }

    public static int delete(String sql, Param param) {
        try (Connection conn = SqlUtil.open()) {
            return executeUpdate(conn, sql, param);
        }
    }

    public static int delete(Connection conn, String sql) {
        return executeUpdate(conn, sql);
    }

    public static int delete(Connection conn, String sql, Param param) {
        return executeUpdate(conn, sql, param);
    }

    public static int update(String sql) {
        try (Connection conn = SqlUtil.open()) {
            return executeUpdate(conn, sql);
        }
    }

    public static int update(String sql, Param param) {
        try (Connection conn = SqlUtil.open()) {
            return executeUpdate(conn, sql, param);
        }
    }

    public static int update(Connection conn, String sql) {
        return executeUpdate(conn, sql);
    }

    public static int update(Connection conn, String sql, Param param) {
        return executeUpdate(conn, sql, param);
    }

    public static int executeUpdate(String sql) {
        try (Connection conn = SqlUtil.open()) {
            conn.createQuery(sql).executeUpdate();
            int result = conn.getResult();
            return result;
        }
    }

    public static int executeUpdate(Connection conn, String sql) {
        conn.createQuery(sql).executeUpdate();
        int result = conn.getResult();
        return result;
    }

    public static int executeUpdate(Connection conn, String sql, Param param) {
        Query query = conn.createQuery(sql);
        for (Param.NameValue nv : param.params()) {
            query.addParameter(nv.getName(), nv.getValue());
        }
        query.executeUpdate();
        int result = conn.getResult();
        return result;
    }

    public static Connection open() {
        return sql2o.open();
    }

    public static Connection beginTransaction() {
        return sql2o.beginTransaction();
    }

    private SqlUtil() {
    }

    public static void main(String[] args) {
        init();

        executeUpdate("drop table if exists `" + "job" + "`;");

        executeUpdate(Sql.CREATE_TBL_JOB);
    }

}
